package com.ujiuye.dao.impl;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.ujiuye.dao.CourseUserDao;
import com.ujiuye.entity.Course;
import com.ujiuye.entity.CourseUser;
import com.ujiuye.entity.User;
import com.ujiuye.util.PageBean;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class CourseUserDaoImpl implements CourseUserDao {
    QueryRunner qr = new QueryRunner(new ComboPooledDataSource());
    @Override
    public int totalCount() {
        String sql = "select count(*) from course_user";
        try {
            Long num = qr.query(sql, new ScalarHandler<Long>());
            return num.intValue();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }


    public List<CourseUser> findAllCs(int startIndex, int pageSize) {
        String sql = "select id,c.cid,u.uid,courseName,phone,courseType,coursePrice,name from course c,user u,course_user cu\n" +
                "where c.cid = cu.cid and u.uid = cu.uid limit ?,?";

        List<CourseUser> lists = new ArrayList<>();
        try {
            List<Map<String, Object>> list = qr.query(sql, new MapListHandler(),startIndex,pageSize);
            for(Map<String, Object> map:list){
                Course course = new Course();
                course.setCid((int)map.get("cid"));
                course.setCourseName((String)map.get("courseName"));
                course.setCoursePrice((map.get("coursePrice")).toString());
                course.setCourseType((int)map.get("courseType"));

                User user = new User();
                user.setUid((int)map.get("uid"));
                user.setName((String)map.get("name"));
                user.setPhone((String)map.get("phone"));


                CourseUser cu = new CourseUser();
                cu.setId((int)map.get("id"));
                cu.setCourse(course);
                cu.setUser(user);

                lists.add(cu);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return lists;
    }

    @Override
    public int delAll(String ids) {
        String sql = "delete from course_user where id in("+ids+")";
        try {
            return qr.update(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public List<Course> findAllCourse() {
        String sql = "select * from course";
        try {
           return qr.query(sql,new BeanListHandler<Course>(Course.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int updateCourseById(int id, int cid) {
        String sql = "update course_user set cid = ? where id= ?";
        try {
           return qr.update(sql,cid,id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int insertCourseUser(int uid, int cid) {
        String sql = "insert into course_user(uid,cid) values(?,?)";
        try {
           return qr.update(sql,uid,cid);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public CourseUser findCourseUser(int uid, int cid) {
        String sql = "select * from course_user where uid = ? and cid = ?";
        try {
           return qr.query(sql,new BeanHandler<CourseUser>(CourseUser.class),uid,cid);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }


}
